package com.XinYun.Library.modules.admin.common;

public enum SqlMapper {

    //获得存留请求
    GET_SAVED_MSG("SELECT `bookname`,`bid` FROM book_id WHERE `state`= 1"),
    //获得借阅请求
    GET_LOANED_MSG("SELECT * FROM user_loan WHERE `state`= 0"),
    //获得逾期请求
    GET_OUTTIME_MSG("SELECT * FROM user_loan WHERE `state`= -1"),
    //获得存留请求数
    GET_SAVED_MSG_NUM("SELECT Count(*) FROM book_id WHERE `state`= 1"),
    //获得借阅请求数
    GET_LOANED_MSG_NUM("SELECT Count(*) FROM user_loan WHERE `state`= 0"),
    //获得逾期请求数
    GET_OUTTIME_MSG_NUM("SELECT Count(*) FROM user_loan WHERE `state`= -1"),
    //获得用户管理联合请求
    UPDATE_USER_MSG("UPDATE user_info SET isbanned= ? WHERE sid = ?"),
    //搜索用户的信息
    GET_ALLUSER_MSG("""
                 SELECT
                    user_info.username,
                    user_info.sid,
                    borrow_count AS bcnt,
                    return_count AS rcnt,
                    exceedtime_count AS ecnt,
                    mail,
                    user_loandata.isbanned
                 FROM
                 	user_info
                 LEFT JOIN user_loandata ON (user_loandata.sid = user_info.sid)
                 """),
    //搜索用户的信息
    GET_ALLUSER_MSG_NUM("""
                 SELECT
                    Count(*)
                 FROM
                 	user_info
                 LEFT JOIN user_loandata ON (user_loandata.sid = user_info.sid)
                 """),
    //搜索没被ban用户的信息
    GET_NORMALUSER_MSG("""
                 SELECT
                    user_info.username,
                    user_info.sid,
                    borrow_count AS bcnt,
                    return_count AS rcnt,
                    exceedtime_count AS ecnt,
                    mail,
                    user_loandata.isbanned
                 FROM
                 	user_info
                 LEFT JOIN user_loandata ON (user_loandata.sid = user_info.sid)
                   WHERE user_info.isbanned = 0
                 """),
    //搜索没被ban用户的信息
    GET_NORMALUSER_MSG_NUM("""
                 SELECT
                    Count(*)
                 FROM
                 	user_info
                 LEFT JOIN user_loandata ON (user_loandata.sid = user_info.sid)
                   WHERE user_info.isbanned = 0
                 """),
    //搜索ban了的用户的信息

    GET_BANNEDUSER_MSG("""
                 SELECT
                    user_info.username,
                    user_info.sid,
                    borrow_count AS bcnt,
                    return_count AS rcnt,
                    exceedtime_count AS ecnt,
                    mail,
                    user_loandata.isbanned
                 FROM
                 	user_info
                 LEFT JOIN user_loandata ON (user_loandata.sid = user_info.sid)
                  WHERE user_info.isbanned = 1
                 """),
    //搜索ban了的用户的信息
    GET_BANNEDUSER_MSG_NUM("""
                 SELECT
                    Count(*)
                 FROM
                 	user_info
                 LEFT JOIN user_loandata ON (user_loandata.sid = user_info.sid)
                  WHERE user_info.isbanned = 1
                 """),
    //获得书本信息
    GET_BOOK_INFO("""
            SELECT
                bookname,
                now,
                `all`
            FROM		
            (SELECT
                b1.bookname AS bookname ,
                now,
                `all`
            FROM
              (SELECT
                    bookname,
                    num AS `all`
                 FROM
                    books_info)AS b1
            INNER JOIN
                (SELECT
                    bookname,
                    COUNT(state = 1 AND visible = 1 OR NULL) AS now
                 FROM
                    book_id
                 GROUP BY
                    bookname) AS b2
            ON
                (b1.bookname=b2.bookname)
            ORDER BY b1.bookname)AS b3
                  """),
    //获取书大类数目
    GET_BOOK_INFO_NUM( """
            SELECT
                  Count(*)
              FROM		
              (SELECT
                  b1.bookname AS bookname ,
                  now,
                  `all`
              FROM
                (SELECT
                      bookname,
                      num AS `all`
                   FROM
                      books_info)AS b1
              INNER JOIN
                  (SELECT
                      bookname,
                      COUNT(state = 1 AND visible = 1 OR NULL) AS now
                   FROM
                      book_id
                   GROUP BY
                      bookname) AS b2
              ON
                  (b1.bookname=b2.bookname)
              ORDER BY b1.bookname)AS b3
                        """),
    //搜索了的用户的信息
    GET_USER_HISTORY("SELECT bookname,bid,state,starttime,endtime FROM user_loan WHERE `sid`= ?");

    SqlMapper(String sql) {
        this.sql = sql;
    }

    public String getSql(){
        return this.sql;
    }

    final String sql;
}
